using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Collections.Generic;

namespace Lzfy_Refund_Service.Repositories
{
    public class RefundMasterRepository : IRefundMasterRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;

        public RefundMasterRepository(IDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<RefundMaster?> GetByIdAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master WHERE id = @Id";
            return await connection.QueryFirstOrDefaultAsync<RefundMaster>(sql, new { Id = id });
        }

        public async Task<RefundMaster?> GetByRelIdAsync(string relId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master WHERE rel_id = @RelId";
            return await connection.QueryFirstOrDefaultAsync<RefundMaster>(sql, new { RelId = relId });
        }

        public async Task<IEnumerable<RefundMaster>> GetAllAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master ORDER BY input_date ASC";
            return await connection.QueryAsync<RefundMaster>(sql);
        }

        public async Task<(IEnumerable<RefundMaster> data, int totalCount)> GetRefundsWithFilterAsync(
            DateTime? startDate = null,
            DateTime? endDate = null,
            int? auditStatus = null,
            string? patientId = null,
            string? patientName = null,
            int pageIndex = 1,
            int pageSize = 20)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();
            
            // 构建WHERE条件
            if (startDate.HasValue)
            {
                whereConditions.Add("input_date >= @StartDate");
                parameters.Add("StartDate", startDate.Value);
            }
            
            if (endDate.HasValue)
            {
                var endDateTime = endDate.Value.Date.AddDays(1).AddTicks(-1);
                whereConditions.Add("input_date <= @EndDate");
                parameters.Add("EndDate", endDateTime);
            }
            
            if (auditStatus.HasValue)
            {
                whereConditions.Add("audit_result = @AuditStatus");
                parameters.Add("AuditStatus", auditStatus.Value.ToString());
            }
            
            if (!string.IsNullOrEmpty(patientId))
            {
                whereConditions.Add("patient_id LIKE @PatientId");
                parameters.Add("PatientId", $"%{patientId}%");
            }
            
            if (!string.IsNullOrEmpty(patientName))
            {
                whereConditions.Add("pat_name LIKE @PatientName");
                parameters.Add("PatientName", $"%{patientName}%");
            }
            
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            
            // 查询总数
            var countSql = $"SELECT COUNT(*) FROM lzfy_refund_master {whereClause}";
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
            
            // 查询分页数据
            var offset = (pageIndex - 1) * pageSize;
            parameters.Add("Offset", offset);
            parameters.Add("PageSize", pageSize);
            
            var dataSql = $@"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master 
                {whereClause}
                ORDER BY input_date ASC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            
            var data = await connection.QueryAsync<RefundMaster>(dataSql, parameters);
            
            return (data, totalCount);
        }

        public async Task<(IEnumerable<RefundMaster> data, int totalCount)> GetRefundStatisticsAsync(
            DateTime? startDate = null,
            DateTime? endDate = null,
            int? auditStatus = null,
            string? applicantType = null,
            string? patientId = null,
            string? patientName = null,
            string? bankCardHolder = null,
            int pageIndex = 1,
            int pageSize = 20)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();
            
            // 构建WHERE条件
            if (startDate.HasValue)
            {
                whereConditions.Add("input_date >= @StartDate");
                parameters.Add("StartDate", startDate.Value);
            }
            
            if (endDate.HasValue)
            {
                var endDateTime = endDate.Value.Date.AddDays(1).AddTicks(-1);
                whereConditions.Add("input_date <= @EndDate");
                parameters.Add("EndDate", endDateTime);
            }
            
            if (auditStatus.HasValue)
            {
                whereConditions.Add("audit_result = @AuditStatus");
                parameters.Add("AuditStatus", auditStatus.Value.ToString());
            }
            
            if (!string.IsNullOrEmpty(applicantType))
            {
                if (applicantType.ToLower() == "self")
                {
                    whereConditions.Add("(public_agent_name IS NULL OR public_agent_name = '')");
                }
                else if (applicantType.ToLower() == "proxy")
                {
                    whereConditions.Add("(public_agent_name IS NOT NULL AND public_agent_name != '')");
                }
            }
            
            if (!string.IsNullOrEmpty(patientId))
            {
                whereConditions.Add("patient_id LIKE @PatientId");
                parameters.Add("PatientId", $"%{patientId}%");
            }
            
            if (!string.IsNullOrEmpty(patientName))
            {
                whereConditions.Add("pat_name LIKE @PatientName");
                parameters.Add("PatientName", $"%{patientName}%");
            }
            
            if (!string.IsNullOrEmpty(bankCardHolder))
            {
                whereConditions.Add("bank_card_holder LIKE @BankCardHolder");
                parameters.Add("BankCardHolder", $"%{bankCardHolder}%");
            }
            
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            
            // 查询总数
            var countSql = $"SELECT COUNT(*) FROM lzfy_refund_master {whereClause}";
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
            
            // 查询分页数据
            var offset = (pageIndex - 1) * pageSize;
            parameters.Add("Offset", offset);
            parameters.Add("PageSize", pageSize);
            
            var dataSql = $@"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master 
                {whereClause}
                ORDER BY input_date ASC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            
            var data = await connection.QueryAsync<RefundMaster>(dataSql, parameters);
            
            return (data, totalCount);
        }

        public async Task<IEnumerable<RefundMaster>> GetByPatientIdAsync(string patientId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master WHERE patient_id = @PatientId ORDER BY input_date ASC";
            return await connection.QueryAsync<RefundMaster>(sql, new { PatientId = patientId });
        }

        public async Task<IEnumerable<RefundMaster>> GetByPatientIdAndDateRangeAsync(string patientId, int days)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master 
                WHERE patient_id = @PatientId AND input_date >= DATEADD(day, -@Days, GETDATE()) 
                ORDER BY input_date ASC";
            return await connection.QueryAsync<RefundMaster>(sql, new { PatientId = patientId, Days = days });
        }

        public async Task<int> CreateAsync(RefundMaster refundMaster)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                INSERT INTO lzfy_refund_master (
                    patient_id, pat_name, id_number, phone_number, 
                    refund_bank_name, refund_bank_card_no, refund_bank_open_name, refund_bank_city, 
                    refund_amount, public_agent_name, public_agent_phone_number, public_agent_relation, 
                    public_agent_relation_id, public_agent_refund_bank_name, public_agent_refund_bank_card_no, 
                    public_agent_refund_bank_open_name, public_agent_refund_bank_city, 
                    refund_type_name, refund_type_id, rel_id, audit_result, bank_card_holder, refund_balance_sn, acct_id
                ) VALUES (
                    @PatientId, @PatName, @IdNumber, @PhoneNumber, 
                    @RefundBankName, @RefundBankCardNo, @RefundBankOpenName, @RefundBankCity, 
                    @RefundAmount, @PublicAgentName, @PublicAgentPhoneNumber, @PublicAgentRelation, 
                    @PublicAgentRelationId, @PublicAgentRefundBankName, @PublicAgentRefundBankCardNo, 
                    @PublicAgentRefundBankOpenName, @PublicAgentRefundBankCity, 
                    @RefundTypeName, @RefundTypeId, @RelId, '0', @BankCardHolder, @RefundBalanceSn, @AcctId
                );
                SELECT CAST(SCOPE_IDENTITY() as int);";
            
            return await connection.QuerySingleAsync<int>(sql, refundMaster);
        }

        public async Task<bool> UpdateAsync(RefundMaster refundMaster)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                UPDATE lzfy_refund_master SET 
                    patient_id = @PatientId, pat_name = @PatName, id_number = @IdNumber, phone_number = @PhoneNumber, 
                    refund_bank_name = @RefundBankName, refund_bank_card_no = @RefundBankCardNo, 
                    refund_bank_open_name = @RefundBankOpenName, refund_bank_city = @RefundBankCity, 
                    refund_amount = @RefundAmount, public_agent_name = @PublicAgentName, 
                    public_agent_phone_number = @PublicAgentPhoneNumber, public_agent_relation = @PublicAgentRelation, 
                    public_agent_relation_id = @PublicAgentRelationId, 
                    public_agent_refund_bank_name = @PublicAgentRefundBankName, 
                    public_agent_refund_bank_card_no = @PublicAgentRefundBankCardNo, 
                    public_agent_refund_bank_open_name = @PublicAgentRefundBankOpenName, 
                    public_agent_refund_bank_city = @PublicAgentRefundBankCity, 
                    refund_type_name = @RefundTypeName, refund_type_id = @RefundTypeId, bank_card_holder = @BankCardHolder, refund_balance_sn = @RefundBalanceSn, acct_id = @AcctId
                WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, refundMaster);
            return rowsAffected > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "DELETE FROM lzfy_refund_master WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            return rowsAffected > 0;
        }

        public async Task<bool> UpdateAuditAsync(int id, RefundMasterUpdateRequest request)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                UPDATE lzfy_refund_master SET 
                    audit_user_id = @AuditUserId,
                    audit_user_name = @AuditUserName,
                    audit_result = @AuditResult, 
                    audit_date = GETDATE(), 
                    audit_comment = @AuditComment,
                    refund_balance = @RefundBalance,
                    refund_balance_sn = @RefundBalanceSn,
                    acct_id = @AcctId
                WHERE id = @Id";
            
            var parameters = new
            {
                Id = id,
                request.AuditUserId,
                request.AuditUserName,
                request.AuditResult,
                request.AuditComment,
                request.RefundBalance,
                request.RefundBalanceSn,
                request.AcctId
            };
            
            var rowsAffected = await connection.ExecuteAsync(sql, parameters);
            return rowsAffected > 0;
        }

        public async Task<bool> UpdateAuditWithConcurrencyCheckAsync(int id, RefundMasterUpdateRequest request)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                UPDATE lzfy_refund_master SET 
                    audit_user_id = @AuditUserId,
                    audit_user_name = @AuditUserName,
                    audit_result = @AuditResult, 
                    audit_date = GETDATE(), 
                    audit_comment = @AuditComment,
                    refund_balance = @RefundBalance,
                    refund_balance_sn = @RefundBalanceSn,
                    acct_id = @AcctId,
                    his_refund_status = @HisRefundStatus
                WHERE id = @Id 
                  AND (audit_result IS NULL OR audit_result = '' OR audit_result = '0')";
            
            var parameters = new
            {
                Id = id,
                request.AuditUserId,
                request.AuditUserName,
                request.AuditResult,
                request.AuditComment,
                request.RefundBalance,
                request.RefundBalanceSn,
                request.AcctId,
                request.HisRefundStatus
            };
            
            var rowsAffected = await connection.ExecuteAsync(sql, parameters);
            return rowsAffected > 0;
        }

        public async Task<bool> UpdateHisRefundStatusAsync(int id, string hisRefundStatus)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                UPDATE lzfy_refund_master SET 
                    his_refund_status = @HisRefundStatus,
                    his_refund_status_update_time = GETDATE()
                WHERE id = @Id";
            
            var parameters = new
            {
                Id = id,
                HisRefundStatus = hisRefundStatus
            };
            
            var rowsAffected = await connection.ExecuteAsync(sql, parameters);
            return rowsAffected > 0;
        }

        public async Task<(IEnumerable<RefundMaster> data, int totalCount)> GetRefundsPagedAsync(int pageNumber, int pageSize, DateTime? startDate, DateTime? endDate, string? auditResult, string? settleStatus, string? bankCardHolder = null, string? auditUserName = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();
            
            // 构建WHERE条件
            if (startDate.HasValue)
            {
                whereConditions.Add("input_date >= @StartDate");
                parameters.Add("StartDate", startDate.Value);
            }
            
            if (endDate.HasValue)
            {
                whereConditions.Add("input_date <= @EndDate");
                parameters.Add("EndDate", endDate.Value);
            }
            
            if (!string.IsNullOrEmpty(auditResult))
            {
                whereConditions.Add("audit_result = @AuditResult");
                parameters.Add("AuditResult", auditResult);
            }
            
            // 添加结算状态过滤条件
            if (!string.IsNullOrEmpty(settleStatus))
            {
                if (settleStatus == "0") // 未结算
                {
                    whereConditions.Add("(acct_id IS NULL OR acct_id = '')");
                }
                else if (settleStatus == "1") // 已结算
                {
                    whereConditions.Add("(acct_id IS NOT NULL AND acct_id != '')");
                }
            }
            
            // 添加银行卡持有人过滤条件
            if (!string.IsNullOrEmpty(bankCardHolder))
            {
                whereConditions.Add("bank_card_holder LIKE @BankCardHolder");
                parameters.Add("BankCardHolder", $"%{bankCardHolder}%");
            }
            
            // 添加审核人过滤条件
            if (!string.IsNullOrEmpty(auditUserName))
            {
                whereConditions.Add("audit_user_name LIKE @AuditUserName");
                parameters.Add("AuditUserName", $"%{auditUserName}%");
            }
            
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            
            // 查询总数
            var countSql = $"SELECT COUNT(*) FROM lzfy_refund_master {whereClause}";
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
            
            // 查询分页数据
            var offset = (pageNumber - 1) * pageSize;
            parameters.Add("Offset", offset);
            parameters.Add("PageSize", pageSize);
            
            var dataSql = $@"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master 
                {whereClause}
                ORDER BY input_date DESC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            
            var data = await connection.QueryAsync<RefundMaster>(dataSql, parameters);
            
            return (data, totalCount);
        }

        public async Task<IEnumerable<RefundMaster>> GetByAcctIdAsync(string acctId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                pat_name AS PatName,
                id_number AS IdNumber,
                phone_number AS PhoneNumber,
                refund_bank_name AS RefundBankName,
                refund_bank_card_no AS RefundBankCardNo,
                refund_bank_open_name AS RefundBankOpenName,
                refund_bank_city AS RefundBankCity,
                refund_amount AS RefundAmount,
                public_agent_name AS PublicAgentName,
                public_agent_phone_number AS PublicAgentPhoneNumber,
                public_agent_relation AS PublicAgentRelation,
                public_agent_relation_id AS PublicAgentRelationId,
                public_agent_refund_bank_name AS PublicAgentRefundBankName,
                public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                audit_user_id AS AuditUserId,
                audit_user_name AS AuditUserName,
                audit_result AS AuditResult,
                audit_date AS AuditDate,
                refund_type_name AS RefundTypeName,
                refund_type_id AS RefundTypeId,
                audit_comment AS AuditComment,
                rel_id AS RelId,
                bank_card_holder AS BankCardHolder,
                refund_balance AS RefundBalance,
                refund_balance_sn AS RefundBalanceSn,
                acct_id AS AcctId
                FROM lzfy_refund_master 
                WHERE acct_id = @AcctId
                ORDER BY input_date DESC";
            return await connection.QueryAsync<RefundMaster>(sql, new { AcctId = acctId });
        }

        public async Task<bool> UpdateAcctIdWithConcurrencyCheckAsync(int id, string acctId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                UPDATE lzfy_refund_master 
                SET acct_id = @AcctId
                WHERE id = @Id 
                  AND audit_result = '1' 
                  AND (acct_id IS NULL OR acct_id = '')";
            
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id, AcctId = acctId });
            return rowsAffected > 0;
        }
    }
}